﻿'========================================================================
'功能:網頁名稱及相關設定資料庫存取物件
'作者:andy
'日期: 2011/1/28
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data


Namespace OAMS.DAO
    Public Class MenuDAO
        Inherits BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        ''' <summary>
        ''' 新增一筆Menu資料
        ''' </summary>
        ''' <param name="RootName"></param>
        ''' <param name="Name"></param>
        ''' <param name="NeedControl"></param>
        ''' <param name="URL"></param>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <returns>新增一筆Menu資料</returns>
        ''' <remarks>新增一筆Menu資料</remarks>
        Public Function INSERT_NewOne(ByVal RootName As String, ByVal Name As String, ByVal NeedControl As Boolean, ByVal URL As String, ByVal GroupID As String, ByVal UID As String,byval IsMenu As Boolean ) As Integer
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}

            sqlParams(0) = New SqlParameter("@RootName", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = RootName
            sqlParams(1) = New SqlParameter("@Name", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = Name
            sqlParams(2) = New SqlParameter("@NeedControl", SqlDbType.Bit)
            sqlParams(2).Value = NeedControl
            sqlParams(3) = New SqlParameter("@URL", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = URL
            sqlParams(4) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = GroupID
            sqlParams(5) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = UID
            sqlParams(6) = New SqlParameter("@IsMenu", SqlDbType.Bit)
            sqlParams(6).Value = IsMenu
            Dim sb As New StringBuilder()


            sb.AppendLine(" declare @ProgramID int select @ProgramID=max(ProgramID)+1  from Menu  INSERT INTO [Menu] ( [ProgramID],[RootName], [Name], [NeedControl], [URL], [GroupID], [CrtUID],IsMenu) VALUES (")
            sb.AppendLine("@ProgramID, @RootName, @Name, @NeedControl, @URL, @GroupID, @UID,@IsMenu) select ProgramID=@ProgramID")
            Return Integer.Parse(SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString)
        End Function

        ''' <summary>
        ''' 取得有授權的Menu資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得有授權的Menu資料</returns>
        ''' <remarks>取得有授權的Menu資料</remarks>
        Public Function GetMenuUID(ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(0).Value = UID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from Menu where GroupID in (select GroupID from GroupMember where UID=@UID) order by OrderByID ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)


        End Function
        ''' <summary>
        ''' 取得畫面檔資料
        ''' </summary>
        ''' <returns>取得畫面檔資料</returns>
        ''' <remarks>取得畫面檔資料</remarks>
        Public Function GetMenu() As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine("Select ProgramID,RootName,Name,NeedControl,URL,GroupID  ,QueryString, ChnName,EngName,IsMenu from Menu  order by ProgramID desc  ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        End Function
        ''' <summary>
        ''' 修改Memu資料
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <param name="RootName"></param>
        ''' <param name="Name"></param>
        ''' <param name="NeedControl"></param>
        ''' <param name="URL"></param>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <remarks>修改Memu資料</remarks>
        Public Sub UpdateMenu(ByVal ProgramID As Integer, ByVal RootName As String, ByVal Name As String, ByVal NeedControl As Boolean, ByVal URL As String, ByVal GroupID As String, ByVal UID As String, ByVal QueryString As Boolean, ByVal ChnName As String, ByVal EngName As String, ByVal IsMenu As Boolean)
            Dim sqlParams As SqlParameter() = New SqlParameter(11) {}
            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            sqlParams(1) = New SqlParameter("@RootName", SqlDbType.NVarChar, 100)
            sqlParams(1).Value = RootName
            sqlParams(2) = New SqlParameter("@Name", SqlDbType.NVarChar, 100)
            sqlParams(2).Value = Name
            sqlParams(3) = New SqlParameter("@NeedControl", SqlDbType.Bit)
            sqlParams(3).Value = NeedControl
            sqlParams(4) = New SqlParameter("@URL", SqlDbType.NVarChar, 100)
            sqlParams(4).Value = URL
            sqlParams(5) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            sqlParams(5).Value = GroupID
            sqlParams(6) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(6).Value = UID
            sqlParams(7) = New SqlParameter("@QueryString", SqlDbType.Bit, QueryString)
            sqlParams(7).Value = QueryString
            sqlParams(8) = New SqlParameter("@ChnName", SqlDbType.NVarChar, 100)
            sqlParams(8).Value = ChnName
            sqlParams(9) = New SqlParameter("@EngName", SqlDbType.NVarChar, 100)
            sqlParams(9).Value = EngName
            sqlParams(10) = New SqlParameter("@IsMenu", SqlDbType.Bit)
            sqlParams(10).Value = IsMenu
            Dim sb As New StringBuilder()
            sb.AppendLine("update Menu  set RootName=@RootName, Name=@Name,ChnName=@ChnName,EngName =@EngName,NeedControl=@NeedControl,URL=@URL , GroupID=@GroupID ,ModiUID=@UID ,ModiDate=GetDate(),QueryString=@QueryString,IsMenu=@IsMenu where ProgramID=@ProgramID")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        ''' <summary>
        ''' 取得某一畫面權限
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <returns>取得某一畫面權限</returns>
        ''' <remarks>取得某一畫面權限</remarks>
        Public Function GetMenuRights(ByVal ProgramID As Integer) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            Dim sb As New StringBuilder()
            sb.AppendLine("Select SeqNo, DisplayName ,0  as canModi from MenuRights where ProgramID=@ProgramID  union select 0 as SeqNo,'' as Display ,1 as canModi order by SeqNo  ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        ''' <summary>
        ''' 新增或修改一筆MenuRights資料
        ''' </summary>
        ''' <param name="ProgramID"></param>
        ''' <param name="SeqNo"></param>
        ''' <param name="DisplayName"></param>
        ''' <param name="UID"></param>
        ''' <remarks>新增或修改一筆MenuRights資料</remarks>
        Public Sub UpdateMenuRights(ByVal ProgramID As Integer, ByVal SeqNo As Integer, ByVal DisplayName As String, ByVal UID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(4) {}
            sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            sqlParams(0).Value = ProgramID
            sqlParams(1) = New SqlParameter("@SeqNo", SqlDbType.Int)
            sqlParams(1).Value = SeqNo
            sqlParams(2) = New SqlParameter("@DisplayName", SqlDbType.NVarChar)
            sqlParams(2).Value = DisplayName
            sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            sqlParams(3).Value = UID

            Dim sb As New StringBuilder()
            If SeqNo = 0 Then
                sb.AppendLine("declare @SeqNo1 int select @SeqNo1=max(SeqNo)+1 from MenuRights where ProgramID=@ProgramID insert into MenuRights (ProgramID,SeqNo,DisplayName,CrtUID,CrtDate,ModiUID,ModiDate)")
                sb.AppendLine("values(@ProgramID,isnull(@SeqNo1,1),@DisplayName,@UID,getdate(),'',null)")
                sb.AppendLine(" select AA=@SeqNo1")
            Else
                sb.AppendLine("update  MenuRights  set DisplayName=@DisplayName,ModiUID=@UID,ModiDate=GetDate()")
                sb.AppendLine("where ProgramID= @ProgramID and SeqNo=@SeqNo")

            End If

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub


    End Class


End Namespace


